package abc.database.interfaces;

import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
import java.util.Map;

public interface MysqlHelper extends ISqlHelper<Map<String, Object>> {
    default List<Map<String, Object>> getTableList(String dbname, JdbcTemplate jdbcTemplate) {
        String sql = String.format( "%s UNION %s;",
                String.format( "SELECT table_name, table_comment, 'table' as type FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='%s' and TABLE_TYPE='BASE TABLE'", dbname ),
                String.format( "SELECT table_name, '' as table_comment, 'view' as type FROM INFORMATION_SCHEMA.views WHERE TABLE_SCHEMA = '%s';", dbname ));

        return jdbcTemplate.queryForList(sql);
    }

    default List<Map<String, Object>> getColumnList(String dbname, JdbcTemplate jdbcTemplate) {
        String sql = String.format("select TABLE_NAME as `table`, COLUMN_NAME as annotationColumnName,COLUMN_COMMENT as comment,DATA_TYPE as dataType, case EXTRA when 'auto_increment' then 1 else 0 end as isAutoIncrement, case EXTRA when  COLUMN_KEY='PRI' then 0 else 1 end as keyFlag,1 as keyIdentityFlag, REPLACE(CONCAT(LOWER(LEFT(COLUMN_NAME, 1)),LOWER(SUBSTRING(COLUMN_NAME, 2))),' , ', ', ') as propertyName, REPLACE(CONCAT(LOWER(LEFT(COLUMN_NAME, 1)),LOWER(SUBSTRING(COLUMN_NAME, 2))),' , ', ', ') as `name`, 1 as `convert`,0 as versionField from information_schema.COLUMNS where TABLE_SCHEMA='%s'", dbname);
        return jdbcTemplate.queryForList(sql);
    }
}
